package ca.llsutherland.nfl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import ca.llsutherland.nfl.domain.Person;
import ca.llsutherland.nfl.exception.ValidationException;
import ca.llsutherland.nfl.util.StringUtil;

public class PersonDao extends BaseDao {

	private static PersonDao instance = new PersonDao();

    @Override
    public String getUnderlyingTableName() {
        return "PERSON";
    }

    public static PersonDao getInstance() {
        return instance ;
    }

    public Person findByName(String name) {
        String findByNameQuery = getFindByNameQuery(name);
        return buildPerson(findByNameQuery);
    }

    private Person buildPerson(String sql) {
        Person person = null;
        ResultSet rs = super.findByQuery(sql);
        
        try {
        	if(rs.next()){
        		person = new Person();
        		person.setId(Long.valueOf(rs.getLong("ID")));
        		person.setFirstname(rs.getString("FIRST_NAME"));
        		person.setLastname(rs.getString("LAST_NAME"));
        		person.setEmail(rs.getString("EMAIL"));
        		person.setLocation(rs.getString("LOCATION"));
        		person.setPhoneNumber(rs.getString("PHONE_NUMBER"));
        	}
        	rs.close();
        } catch (SQLException e) {
        	throw new ValidationException(e.getMessage() + " in buildPerson()");
        } finally{
        	rs = null;
        }
        return person;
    }
    

    public Person create(Person person) {
    	String sql = "INSERT INTO PERSON (FIRST_NAME, LAST_NAME, EMAIL, LOCATION, USER_ID, PHONE_NUMBER) VALUES ("
    		+ StringUtil.addSingleQuotes(person.getFirstname()) + ", "
    		+ StringUtil.addSingleQuotes(person.getLastname()) + ", "
    		+ StringUtil.addSingleQuotes(person.getEmail()) + ", "
    		+ StringUtil.addSingleQuotes(person.getLocation()) + ", "
    		+ person.getUserId() + ", "
    		+ StringUtil.addSingleQuotes(person.getPhoneNumber()) + ");";
        return findById(super.create(sql));
    }

    public Person findById(Long id) {
        String findByIdQuery = getFindByIdQuery(id);
        return buildPerson(findByIdQuery);
    }

}
